package com.itheima.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.itheima.domain.Category;
import com.itheima.domain.Order;
import com.itheima.domain.OrderItem;
import com.itheima.domain.Product;
import com.itheima.utils.DataSourceUtils;
import java.sql.Connection;

public class ProductDao {

	public List<Product> findHotProduct() {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from product where is_hot=? limit ?,?";
		try {
			return runner.query(sql, new BeanListHandler<Product>(Product.class), 1,0,9);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Product> findLastProduct() {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from product order by pdate desc limit ?,?";
		try {
			return runner.query(sql, new BeanListHandler<Product>(Product.class),0,9);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Category> findAllCategory() {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category";
		try {
			return runner.query(sql, new BeanListHandler<Category>(Category.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public int getCount(String cid) {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from product where cid=?";
		try {
			
				Long count= (Long) runner.query(sql, new ScalarHandler(),cid);
				return count.intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
		
	}
	
	public List<Product> productById(int current) {
		// TODO Auto-generated method stub
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from product order by pdate desc limit ?,?";
		try {
			return runner.query(sql, new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Product> findProductByPage(String cid, int index, int currentCount) {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from product where cid=? limit ?,?";
		try {
			return runner.query(sql, new BeanListHandler<Product>(Product.class),cid,index,currentCount);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public Product findProductById(String pid) {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from product where pid=?";
		try {
			return runner.query(sql, new BeanHandler<Product>(Product.class), pid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	
	}

	//向orders表插入数据
		public void addOrders(Order order)  {
			QueryRunner runner = new QueryRunner();
			String sql = "insert into orders values(?,?,?,?,?,?,?,?)";
			
			try {
				Connection conn = DataSourceUtils.getConnection();
				runner.update(conn,sql, order.getOid(),order.getOrdertime(),order.getTotal(),order.getState(),
						order.getAddress(),order.getName(),order.getTelephone(),order.getUser().getUid());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		//向orderitem表插入数据
		public void addOrderItem(Order order)  {
			QueryRunner runner = new QueryRunner();
			String sql = "insert into orderitem values(?,?,?,?,?)";
			Connection conn;
			try {
				conn = DataSourceUtils.getConnection();
				List<OrderItem> orderItems = order.getOrderItems();
				for(OrderItem item : orderItems){
					runner.update(conn,sql,item.getItemid(),item.getCount(),item.getSubtotal(),item.getProduct().getPid(),item.getOrder().getOid());
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			
		}

		public void updateOrderAdrr(Order order) throws SQLException {
			QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "update orders set address=?,name=?,telephone=? where oid=?";
			runner.update(sql, order.getAddress(),order.getName(),order.getTelephone(),order.getOid());
		}

		public void updateOrderState(String r6_Order) throws SQLException {
			QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "update orders set state=? where oid=?";
			runner.update(sql, 1,r6_Order);
		}

		public List<Object> findProductByWord(String word) throws SQLException {
			QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "select * from product where pname like ? limit 0,8";
			List<Object> query = runner.query(sql, new ColumnListHandler("pname"), "%"+word+"%");
			return query;
		}
		
		public List<Order> findAllOrders(String uid) throws SQLException {
			QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "select * from orders where uid=?";
			return runner.query(sql, new BeanListHandler<Order>(Order.class), uid);
		}

		public List<Map<String, Object>> findAllOrderItemByOid(String oid) throws SQLException {
			QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "select i.count,i.subtotal,p.pimage,p.pname,p.shop_price from orderitem i,product p where i.pid=p.pid and i.oid=?";
			List<Map<String, Object>> mapList = runner.query(sql, new MapListHandler(), oid);
			return mapList;
		}
}

